/**
 * 
 */
package com.jie.eclipse.pan.dao;

import java.net.InetAddress;
import java.net.UnknownHostException;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.eclipse.jface.preference.IPreferenceStore;

import com.jie.eclipse.pan.Activator;
import com.jie.eclipse.pan.data.BaseDataEntities;
import com.jie.eclipse.pan.data.BaseDataEntity;
import com.jie.eclipse.pan.preferences.PreferenceConstants;
import com.jie.eclipse.pan.utils.StringBuilderEx;
import com.jie.eclipse.pan.utils.StringHelper;
import com.jie.eclipse.pan.utils.Utils;

/**
 * @author yanjie
 *
 */
public class QMDao extends BaseDao{
	private Map<String, String> classDialogMap = null;//xtype 对应对话框
	private Map<String, String> classFormItemMap = null;//xtype对应 formitem
	private Map<String, String> classDFMap = null;//对话框对应formitem
	private LinkedHashMap<String, String> editorMap = null;//名称对应 formitem eg:下拉框	select
	private IPreferenceStore store = null;//首选项
	private BaseDataEntity icaption = null;//标题
	private String strLogin = "";//登陸字符串
	private BaseDataEntity panConfig = null; 
	private Map<String, String> sysNameMap = null;//pan..pan_table 资料
	private String[][] fzr = null;
	
	private boolean breload = false;
	protected static QMDao qmDao = null;
	public static QMDao getInstance(){
		if(qmDao == null){
			qmDao = new QMDao();
			if(Activator.getDefault() != null)
				qmDao.setStore(Activator.getDefault().getPreferenceStore());
		}
		return qmDao;
	}
	
	private Map<String, String> jspInfoMap = null;
	public Map<String, String> getJspInfoMap(){
		if(jspInfoMap == null){initJspInfoMap();}
		return jspInfoMap;
	}
	
	private void initJspInfoMap(){
		jspInfoMap = new HashMap<String, String>();
		Utils utils = new Utils();
		try {
			ResultSet rs = utils.executeQuery(getPanDataSource(), "select pagePath,pageName from pan..PAN_PAGE  order by srt");
			while(rs.next()){
				jspInfoMap.put(utils.getRsString(rs, "pagePath"), utils.getRsString(rs, "pageName"));
			}
			rs.close();
		} catch (Exception e) {
				//e.printStackTrace();
		}finally{
			utils.cleanUp();
		}
	}
	
	public void  registerJspInfo(String path,String pageName,String owner,String ip,boolean bOverWrite){
		Utils utils = new Utils();
		try {
			utils.executeQuery(getPanDataSource(), "exec pan..register_page '"+path+"','"+pageName+"','"+owner+"','"+ip+"','"+(bOverWrite ? "1" : "0")+"'");
		} catch (Exception e) {
				Utils.print(e.getLocalizedMessage());
		}finally{
			utils.cleanUp();
		}
	}
	
	public String[][] getPanLogFZR(boolean bsupportEmpty){
		return getPanLogFZR(bsupportEmpty,false);
	}
	
	public String[][] getPanLogFZR(boolean bsupportEmpty,boolean bunknow){
		if(fzr != null) return fzr;
		List<String> list1 = new ArrayList<String>();
		List<String> list2 = new ArrayList<String>();
		if(bsupportEmpty){
			list1.add("--");
			list2.add("");
		}
		Utils utils = new Utils();
		try {
			ResultSet rs = utils.executeQuery(getPanDataSource(), "select staffid ,chname from hr..hr005 where fhr003tsn in (1209,1384,1704,30) and staffid <> '' and leaveday = '' and staffid not in('billy','txlian','helens') order by fhr003tsn desc ,hrnum");
			while(rs.next()){
				list1.add(utils.getRsString(rs, "chname"));
				list2.add(utils.getRsString(rs, "staffid"));
			}
		} catch (Exception e) {
				//e.printStackTrace();
		}finally{
			utils.cleanUp();
		}
		
		if(bunknow){
			list1.add("其它人員");
			list2.add("unknow");
		}
		
		fzr = new String[2][];
		fzr[0]=list1.toArray(new String[0]);
		fzr[1]=list2.toArray(new String[0]);
		return fzr;
	}
	
	public void savePanLog(BaseDataEntity record,BaseDataEntity ret){
		
	}
	
	public static String[] getPanAllDataSource(boolean bsupportEmpty){
		List<String> list = new ArrayList<String>();
		if(bsupportEmpty){
			list.add("--");
		}
		for(int i=1;i<=4;i++){
			String datasource = StringHelper.getValue(QMDao.getInstance().getStore().getString("p_pan_datasource_name"+i),"");
			if(datasource.length() > 0){
				list.add(datasource);
			}
		}
		return list.toArray(new String[0]);
	}
	
	public static Map<String,BaseDataEntity> getPanDataSourceMap(){
		Map<String,BaseDataEntity> map = new HashMap<String, BaseDataEntity>();
		if(QMDao.getInstance().getStore() != null){
			for(int i=1;i<=4;i++){
				String datasource = StringHelper.getValue(QMDao.getInstance().getStore().getString("p_pan_datasource_name"+i),"");
				if(datasource.length() > 0){
					BaseDataEntity row = new  BaseDataEntity();
					row.spsv("datasource",datasource);
					row.spsv("ip",StringHelper.getValue(QMDao.getInstance().getStore().getString("p_pan_datasource_ip"+i),""));
					row.spsv("pwd",StringHelper.getValue(QMDao.getInstance().getStore().getString("p_pan_datasource_pwd"+i),""));
					map.put(datasource,row );
				}
			}
		}else{
			BaseDataEntity row = new  BaseDataEntity();
			row.spsv("datasource","info5");
			row.spsv("ip","169.169.0.6");
			row.spsv("pwd","201008");
			map.put("info5",row );
		}
		return map;
	}
	
	public static String getPanDataSource(){
		if(QMDao.getInstance().getStore() == null )return "info5";
		return StringHelper.getValue(QMDao.getInstance().getStore().getString(PreferenceConstants.P_PAN_DEFAULT_DATASOURCE),"info5");
	}
	
	public BaseDataEntities getColumnsInfo(String serverName,String sql){
		BaseDataEntities rows = new BaseDataEntities();
		if(!StringHelper.IsNullOrEmpty(serverName) && !StringHelper.IsNullOrEmpty(sql)){
			Utils utils = new Utils();
			try {
				BaseDataEntity caption = this.getICaption();
				ResultSet rs = utils.executeQuery(serverName, getTop1Sql(sql));
				ResultSetMetaData metaData = rs.getMetaData();
				for(int i=1;i<=metaData.getColumnCount();i++){
					BaseDataEntity row = new BaseDataEntity();
					String columnName = metaData.getColumnName(i);
					row.spsv("cid", columnName);
					row.spsv("caption", StringHelper.getValue(metaData.getColumnLabel(i), columnName));
					row.spsv("caption", caption.gpsv(row.gpsv("caption"),row.gpsv("caption")));
					rows.add(row);
				}
			} catch (Exception e) {
				Utils.print(e.getMessage());
					//e.printStackTrace();
			}finally{
				utils.cleanUp();
			}
		}
		return rows;
	}
	
	public BaseDataEntities getPageLogRecords(BaseDataEntity params,boolean bdefault){
		BaseDataEntities rows = new BaseDataEntities();
		if(bdefault){
			Calendar cal = Calendar.getInstance();
			cal.setTime(new Date());
			cal.add(Calendar.MONTH, -1);
			SimpleDateFormat sf = new SimpleDateFormat("yyyy/MM/dd");
			params.spsv("day1", sf.format(cal.getTime()));
			params.spsv("day2", sf.format(new Date()));
		}
		Utils utils = new Utils();
		try {
			//查询条件
			StringBuilderEx sql =  new StringBuilderEx("select top "+(params.gpsv("top","100"))+" case"
					+" when isnull(a.[owner],'') <> '' then a.[owner]"
					+" when isnull(b.agent,'') <> '' then b.agent"
					+" when isnull(b.[owner],'') <> '' then b.[owner]"
					+" else 'yanjie' end as [owner],"
				+" isnull(b.pagename,a.pagepath) as pagename,"
				+" b.sn as pagesn,a.*"
				+" from pan..pan_log a left join pan..pan_page b on a.pagepath = b.pagepath"
				+" where a.enab = 1 ");
				
			if(!params.isNullOrEmpty("pagePath")){
				sql.Append(" and a.pagepath like '%%%1$s%%' ",params.gpsv("pagePath"));
			}
			
			if(!params.isNullOrEmpty("day1")){
				sql.Append(" and convert(char(10),a.crtday,111) >= '%1$s' ",params.gpsv("day1"));
			}
			
			if(!params.isNullOrEmpty("day2")){
				sql.Append(" and convert(char(10),a.crtday,111) <= '%1$s' ",params.gpsv("day2"));
			}
			
			if(!params.isNullOrEmpty("status_code")){
				sql.Append(" and a.status = '%1$s' ",params.gpsv("status_code"));
			}
			
			if(!params.isNullOrEmpty("owner")){
				if(!"unknow".equals(params.gpsv("owner"))){
					sql.Append(" and case when isnull(a.[owner],'') <> '' then a.[owner] when isnull(b.agent,'') <> '' then b.agent when isnull(b.[owner],'') <> '' then b.[owner] else 'yanjie' end = '%1$s' ",params.gpsv("owner"));
				}else{
					sql.Append(" and case when isnull(a.[owner],'') <> '' then a.[owner] when isnull(b.agent,'') <> '' then b.agent when isnull(b.[owner],'') <> '' then b.[owner] else 'yanjie' end not in(select staffid from hr..hr005 where fhr003tsn in (1209,1384,1704,30) and staffid <> '' and leaveday = '' and staffid not in('billy','txlian','helens')) ");
				}
			}
			
			if(!params.isNullOrEmpty("msg")){
				sql.Append(" and a.detail like '%%%1$s%%' ",params.gpsv("msg"));
			}
			
			
			sql.Append(" order by case when isnull(a.[owner],'') <> '' then a.[owner] when isnull(b.agent,'') <> '' then b.agent when isnull(b.[owner],'') <> '' then b.[owner] else 'yanjie' end,a.sn desc");
			
			SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm");
			ResultSet rs = utils.executeQuery("info5", sql.toString());
			while(rs.next()){
				BaseDataEntity row = new BaseDataEntity();
				row.FromResultSet(rs, false);
				row.spsv("status", "0".equals(utils.getRsString(rs, "status"))?"未處理":"已處理");
				row.spsv("status_code", utils.getRsString(rs, "status"));
				row.spsv("crtday", df.format((Date)row.gpov("crtday")));
				row.spsv("updday", df.format((Date)row.gpov("updday")));
				rows.add(row);
			}
		} catch (Exception e) {
			Utils.print(e.getMessage());
				//e.printStackTrace();
		}finally{
			utils.cleanUp();
		}
		return rows;
	}
	
	public String getTop1Sql (String sql){
		if(!(sql.startsWith("select top") || sql.startsWith("select   top"))){
			sql = sql.replaceFirst("select", "select top 1");
		}
		
		return sql;
	}
	
	/**
	 * @return
	 * 返回靜態標籤
	 */
	public BaseDataEntities getPanTags(String type){
		BaseDataEntities rows = null ;
		Utils utils = new Utils();
		try {
			String whr = " type='"+type+"'";
			if("custom".equalsIgnoreCase(type)){
				if(!"all".equals(getCustomUserName())){
					whr += " and crtuserid in("+StringHelper.arrayToString(getCustomUserName().split(","), "'", ",")+")";
				}
			}
			
			ResultSet rs = utils.executeQuery(getPanDataSource(), "select * from pan..PAN_HELP_COMPONENT where "+whr+" order by crtuserid,srt");
			rows = BaseDataEntities.FromResultSet(rs);
		} catch (Exception e) {
				//e.printStackTrace();
		}finally{
			utils.cleanUp();
		}
		return rows;
	}
	
	/**
	 * @return
	 * 返回靜態標籤
	 */
	public BaseDataEntities getPanTagTypes(){
		BaseDataEntities rows = null ;
		Utils utils = new Utils();
		try {
			ResultSet rs = utils.executeQuery(getPanDataSource(), "select * from pan..PAN_HELP_COMPONENT_Type  order by srt");
			rows = BaseDataEntities.FromResultSet(rs);
		} catch (Exception e) {
				//e.printStackTrace();
		}finally{
			utils.cleanUp();
		}
		return rows;
	}
	
	public void registerPanICaption(String name,String caption){
		Utils utils = new Utils();
		if(StringHelper.IsNullOrEmpty(name) 
				|| StringHelper.IsNullOrEmpty(caption)
				|| !utils.isChinese(caption)){
			return ;
		}
		try {
			ResultSet rs = null;
			rs = utils.executeQuery(getPanDataSource(), "select * from pan..PAN_ICAPTION  where name= N'"+name+"'");
			StringBuilderEx sql = new StringBuilderEx();
			if(rs.next()){
				sql.Append("update pan..PAN_ICAPTION  set caption = N'%1$s' where sn = '%2$s'" ,caption ,utils.getRsString(rs, "sn"));
			}else{
				sql.Append("insert into pan..pan_icaption (name,caption,crtday,crtname) values('%1$s','%2$s',getdate(),'%3$s')",
						name,
						caption,
						getCurUserName());
			}
			utils.executeUpdate(getPanDataSource(), sql.toString());
		} catch (Exception e) {
				Utils.print(e.getMessage());
		}finally{
			utils.cleanUp();
		}
	}
	
	public BaseDataEntity getICaption(){
		if(icaption == null || breload){
			icaption = new BaseDataEntity();
			Utils utils = new Utils();
			try {
				ResultSet rs = utils.executeQuery(getPanDataSource(), "select * from pan..PAN_ICAPTION ");
				while(rs.next()){
					icaption.spsv(utils.getRsString(rs, "name"), utils.getRsString(rs, "caption"));
				}
			} catch (Exception e) {
					Utils.print(e.getMessage());
			}finally{
				utils.cleanUp();
			}
		}
		return icaption;
	}
	
	public Map<String, String> getXtypeDialogClassMap(){
		if (classDialogMap == null  || breload) {
			classDialogMap = new HashMap<String, String>();
			Utils utils = new Utils();
			try {
				ResultSet rs = utils.executeQuery(getPanDataSource(),"select * from pan..PAN_HELP_COMPONENT where type = 'tag' and xtype <> ''");
				while (rs.next()) {
					classDialogMap.put(utils.getRsString(rs, "xtype"),
							utils.getRsString(rs, "codetp"));
				}
			} catch (Exception e) {
				Utils.print(e.getMessage());
			} finally {
				utils.cleanUp();
			}
		}
		return classDialogMap;
	}
	
	public Map<String, String> getXtypeFormItemClassMap(){
		if (classFormItemMap == null  || breload) {
			classFormItemMap = new HashMap<String, String>();
			Utils utils = new Utils();
			try {
				ResultSet rs = utils.executeQuery(getPanDataSource(),"select * from pan..PAN_HELP_COMPONENT where type = 'tag' and xtype <> ''");
				while (rs.next()) {
					classFormItemMap.put(utils.getRsString(rs, "xtype"),
							utils.getRsString(rs, "formitem"));
				}
			} catch (Exception e) {
				Utils.print(e.getMessage());
			} finally {
				utils.cleanUp();
			}
		}
		return classFormItemMap;
	}
	
	public Map<String, String> getClassDFMap(){
		if (classDFMap == null  || breload) {
			classDFMap = new HashMap<String, String>();
			Utils utils = new Utils();
			try {
				ResultSet rs = utils.executeQuery(getPanDataSource(),"select * from pan..PAN_HELP_COMPONENT where type = 'tag' and xtype <> ''");
				while (rs.next()) {
					classDFMap.put(utils.getRsString(rs, "codetp"),
							utils.getRsString(rs, "formitem"));
				}
			} catch (Exception e) {
				Utils.print(e.getMessage());
			} finally {
				utils.cleanUp();
			}
			
		}
		return classDFMap;
	}
	
	public Map<String, String> getEditorMap(){
		return getEditorMap(true);
	}
	
	public Map<String, String> getEditorMap(boolean bSupportEmpty){
		if (editorMap == null  || breload) {
			editorMap = new LinkedHashMap<String, String>();
			if(bSupportEmpty){
				editorMap.put("--", "");
			}
			
			Utils utils = new Utils();
			try {
				ResultSet rs = utils.executeQuery(getPanDataSource(),"select * from pan..PAN_HELP_COMPONENT where type = 'tag' and xtype <> '' and srt >=7.0  and srt <8 order by srt");
				while (rs.next()) {
					editorMap.put(utils.getRsString(rs, "name"),
							utils.getRsString(rs, "xtype"));
				}
			} catch (Exception e) {
				Utils.print(e.getMessage());
			} finally {
				utils.cleanUp();
			}
		}
		return editorMap;
	}
	public void Refresh(){
		this.breload = true;
		getICaption();
		getXtypeDialogClassMap();
		getXtypeFormItemClassMap();
		getEditorMap();
		getSysNameMap(true);
		this.breload = false;
	}
	
	public IPreferenceStore getStore() {
		return store;
	}

	public void setStore(IPreferenceStore store) {
		this.store = store;
	}
	
	public String getCurUserName(){
		if(this.getStore() == null)
			return System.getProperty("user.name");
		
		String userName =this.getStore().getString(PreferenceConstants.P_NAME);
		if(StringHelper.IsNullOrEmpty(userName))
			return System.getProperty("user.name");
		else
			return userName;
	}
	
	public String getCurLogUserName(){
		String usrname = this.getCurUserName();
		if("sime".equals(usrname)){
			usrname = "songqi";
		}else if("kimi".equals(usrname)){
			usrname = "bill";
		}
		return usrname ;
	}
	
	public String getCustomUserName(){
		if(this.getStore() == null)
			return System.getProperty("user.name");
		
		String userName =this.getStore().getString(PreferenceConstants.P_CUSTOM_NAME);
		if(StringHelper.IsNullOrEmpty(userName))
			return System.getProperty("user.name");
		else
			return userName;
	}
	
	public String getDefaultDataSource(){
		if(this.getStore() == null)
			return "info5";
		
		String defaultDataSource =this.getStore().getString(PreferenceConstants.P_DEFAULT_DATASOURCE);
		if(StringHelper.IsNullOrEmpty(defaultDataSource))
			return "info5";
		else
			return defaultDataSource;
	}
	
	public String getDefaultSQL(){
		if(this.getStore() == null)
			return "select * from ";
		
		String defaultSQL =this.getStore().getString(PreferenceConstants.P_DEFAULT_SQL);
		if(StringHelper.IsNullOrEmpty(defaultSQL))
			return "select * from ";
		else
			return defaultSQL;
	}
	
	public String getCommonColumns(){
		String commonColumns =this.getStore().getString(PreferenceConstants.P_COMMON_COLUMN);
		if(StringHelper.IsNullOrEmpty(commonColumns))
			return "prgName,crtDay,crtName,crtIp,crtHr006tsn,updDay,updName,updIp,updHr006tsn";
		else
			return commonColumns;		
	}
	
	public String getLoginStr(){
		if(StringHelper.IsNullOrEmpty(this.strLogin)){
			this.strLogin = "&sub=OK";
			Utils utils = new Utils();
			try {
				ResultSet rs = utils.executeQuery("info5","select staffid,pwd from hr..hr005 where staffid = '"+this.getCurUserName()+"'");
				if (rs.next()) {
					this.strLogin += "&uid="+utils.getRsString(rs, "staffid");
					this.strLogin += "&pwd="+utils.getRsString(rs, "pwd");
				}
			} catch (Exception e) {
				Utils.print(e.getMessage());
			} finally {
				utils.cleanUp();
			}
		}
		return this.strLogin;
	}
	

	public BaseDataEntity getPanConfig() {
		if(this.panConfig == null){
			this.panConfig = new BaseDataEntity();
		}
		return panConfig;
	}
	
	public String getCaptionByName(String name){
		String caption = "";
		if(StringHelper.IsNullOrEmpty(name))return caption;
		Utils utils = new Utils();
		try {
			
			ResultSet rs = null; 
			if(!this.getPanConfig().isNullOrEmpty("tablesn")){
				String tablesn = this.getPanConfig().gpsv("tablesn");
				rs = utils.executeQuery(getPanDataSource(),"select * from pan..pan_table_detail where tablesn = N'"+tablesn+"' and name = N'"+name+"'");
				if (rs.next()) {
					caption = utils.getRsString(rs, "caption");
				}
			}
			
			if(StringHelper.IsNullOrEmpty(caption)){
				caption = getICaption().gpsv(name);
			}
			
		} catch (Exception e) {
			Utils.print(e.getMessage());
		} finally {
			utils.cleanUp();
		}
		return caption;
	}
	
	public Map getTableDataMap(String sysName){
		Map<String, String> tableDataMap = null ;
		
		Utils utils = new Utils();
		try {
			tableDataMap  = new LinkedHashMap<String, String>();
			tableDataMap.put("--", "");
			
			ResultSet rs =  utils.executeQuery(getPanDataSource(),"select sn ,[database]+'||'+tableName+'||'+rem as tablename from pan..pan_table where [sysname] = N'"+sysName+"' order by srt");
			while (rs.next()) {
				tableDataMap.put(utils.getRsString(rs, "tablename"), utils.getRsString(rs, "sn"));
			}
		} catch (Exception e) {
			Utils.print(e.getMessage());
		} finally {
			utils.cleanUp();
		}
		return tableDataMap;
	}
	
	public Map getSysNameMap(boolean bsupportEmpty){
		if(this.sysNameMap == null){
			Utils utils = new Utils();
			try {
				sysNameMap = new LinkedHashMap<String, String>();
				if(bsupportEmpty){
					sysNameMap.put("--", "");
				}
				ResultSet rs =  utils.executeQuery(getPanDataSource(),"select sysname from pan..pan_table order by srt");
				while (rs.next()) {
					sysNameMap.put(utils.getRsString(rs, "sysname"), utils.getRsString(rs, "sysname"));
				}
			} catch (Exception e) {
				Utils.print(e.getMessage());
			} finally {
				utils.cleanUp();
			}
		}
		return this.sysNameMap;
	}
	
	public String getTableNameBySql(String sql){
		String tablename = ""; 
		try {
			Pattern   pat   =   Pattern.compile("([_a-z0-9]{1,}\\.\\.[_a-z0-9]{1,})");
			Matcher   mat   =pat.matcher(sql);
			if(mat.find()){
				tablename = mat.group();
			}
		} catch (Exception e) {
			return tablename;
		}
		return tablename;
	}
	
	public String getDatanameByTablename(String tablename){
		if(StringHelper.IsNullOrEmpty(tablename))return "";
		String dataname = "";
		String items[] = tablename.replace("..", ",").split(",");
		if(items.length == 2){
			Utils utils = new Utils();
			try {
				ResultSet rs =  utils.executeQuery(getPanDataSource(),"select rem from pan..pan_table where [database] = '"+items[0]+"' and  tablename = '"+items[1]+"'");
				if (rs.next()) {
					dataname =  utils.getRsString(rs, "rem");
					if(dataname.endsWith("檔") || dataname.endsWith("档")){
						dataname = dataname.substring(0, dataname.length()-1);
					}
				}
			} catch (Exception e) {
				Utils.print(e.getMessage());
			} finally {
				utils.cleanUp();
			}
		}
		
		return dataname;
	}
	
	public ResultSet executeQuery(String dataSource,String sql){
		ResultSet rs = null;
		Utils utils = new Utils();
		try {
			rs =  utils.executeQuery(dataSource,sql);
		} catch (Exception e) {
			Utils.print(e.getMessage());
		} finally {
			utils.cleanUp();
		}
		return rs;
	}
	
	public int executeUpdate(String dataSource,String sql){
		int ret = 0;
		Utils utils = new Utils();
		try {
			ret =  utils.executeUpdate(dataSource,sql);
		} catch (Exception e) {
			Utils.print(e.getMessage());
		} finally {
			utils.cleanUp();
		}
		return ret;
	}
	
	public static String getIp(){
		try {
			return InetAddress.getLocalHost().getHostAddress();
		} catch (UnknownHostException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return "";
	}
}
